#! /usr/bin/env python
#coding=utf-8

from uliweb import expose
from sqlalchemy import *
from uliweb.orm import *
from uliweb.utils.pyini import Ini
from sqlalchemy.sql import select
from sqlalchemy.sql import and_, or_, not_
import time, sys
sys.path.insert(0, '../apps')

from departments.models import *
from myuser.models import *

from systems.models import *
from requirements.models import *
from assignments.models import *

from charts.models import *
from tasks.models import *
from tests.models import *

#from departments.models import *
#from requirements.models import *
#from charts.models import *
#from charts.models import *
import datetime
from datetime import date
from get_month_quarter import *
#from __future__ import division

#db = get_connection('sqlite:///../database.db', debug=False)
#调用get_month_quarter.py中get_db_connection()获取数据库联接
db = get_db_connection()

meta = MetaData(db)
db.metadata.reflect()

def run():
    
    #从settings.ini获取任务类型编号
    settings = Ini('../apps/settings.ini')
    
    today = datetime.date.today()
    #assignment_type = dict(settings.PARA.ASSIGNMENT_TYPE)
    req_test = get_model('test')
    req_his = get_model('test_history')
    req_rate = get_model('test_finish_rate')
    #归档历史数据,存在问题，新增部分没有插入历史表
    req = select([req_test.c.id,
                  req_test.c.system,
                  req_test.c.function_name,
                  req_test.c.function_code,
                  req_test.c.function_type,
                  req_test.c.change_status,
                  req_test.c.using_status,
                  req_test.c.function_point,
                  req_test.c.business_degree,
                  req_test.c.trading_frequency,
                  req_test.c.across_system,
                  req_test.c.trading_origin,
                  req_test.c.trading_route,
                  req_test.c.daily_visit,
                  req_test.c.test_version,
#                  req_test.c.function_flag,
#                  req_test.c.test_flag,
                  req_test.c.version_date,
                  req_test.c.test_status,
                  req_test.c.function_endtime,
                  req_test.c.lock_flag,
                  req_test.c.modified_date,
                  req_test.c.modifield_user,
                  req_test.c.subsystem_abbr,
                  req_test.c.subsystem_name],
                and_(req_test.c.version_date >= today,req_test.c.id.in_(select([req_his.c.test_id]))))
    print 'req',req
    req_rs = req.execute()
    data = {}
    for row in req_rs:
        row = list(row)
        
        #print 'firstrow',row
        
        data['test_id']           = row[0]
        data['system']            = row[1] 
        data['function_name']     = row[2] 
        data['function_code']     = row[3] 
        data['function_type']     = row[4] 
        data['change_status']     = row[5] 
        data['using_status']      = row[6] 
        data['function_point']    = row[7] 
        data['business_degree']   = row[8] 
        data['trading_frequency'] = row[9] 
        data['across_system']     = row[10]
        data['trading_origin']    = row[11]
        data['trading_route']     = row[12]
        data['daily_visit']       = row[13]
        data['test_version']      = row[14]
#        data['function_flag']     = row[15]
#        data['test_flag']         = row[16]
        data['version_date']      = row[15]
        data['test_status']       = row[16]
        data['function_endtime']          = row[17]
        data['lock_flag']         = row[18]
        data['modified_date']     = row[19]
        data['modifield_user']    = row[20]
        data['subsystem_abbr']    = row[21]
        data['subsystem_name']    = row[22]
        
        sel_his = select([func.distinct(req_his.c.test_id),req_his.c.modified_date],
                    and_(req_his.c.test_id ==  data['test_id'],
                        or_(                                                    
                            req_his.c.system != data['system'],
                            req_his.c.function_name != data['function_name'],
                            req_his.c.function_code != data['function_code'],
                            req_his.c.function_type != data['function_type'],
                            req_his.c.change_status != data['change_status'],
                            req_his.c.using_status != data['using_status'],
                            req_his.c.function_point != data['function_point'],
                            req_his.c.business_degree != data['business_degree'],
                            req_his.c.trading_frequency != data['trading_frequency'],
                            req_his.c.across_system != data['across_system'],
                            req_his.c.trading_origin != data['trading_origin'],
                            req_his.c.trading_route != data['trading_route'],
                            req_his.c.daily_visit != data['daily_visit'],
                            req_his.c.test_version != data['test_version'],
                            req_his.c.subsystem_abbr != data['subsystem_abbr'],
                            req_his.c.subsystem_name != data['subsystem_name']
                            ),
                        req_his.c.modified_date.in_(select([func.max(req_his.c.modified_date)],
                                                            and_(req_his.c.test_id == data['test_id']))),
                        req_his.c.modified_date < data['modified_date'])
                           )
                            

        #print sel_his
        sel_his_rs = sel_his.execute()
        for row in sel_his_rs:
            print 'firstrow',row,data
            #print 'row_line',row
            r = req_his(**data)
            r.save()
    #------------------------------------
    req = select([req_test.c.id,
                  req_test.c.system,
                  req_test.c.function_name,
                  req_test.c.function_code,
                  req_test.c.function_type,
                  req_test.c.change_status,
                  req_test.c.using_status,
                  req_test.c.function_point,
                  req_test.c.business_degree,
                  req_test.c.trading_frequency,
                  req_test.c.across_system,
                  req_test.c.trading_origin,
                  req_test.c.trading_route,
                  req_test.c.daily_visit,
                  req_test.c.test_version,
#                  req_test.c.function_flag,
#                  req_test.c.test_flag,
                  req_test.c.version_date,
                  req_test.c.test_status,
                  req_test.c.function_endtime,
                  req_test.c.lock_flag,
                  req_test.c.modified_date,
                  req_test.c.modifield_user,
                  req_test.c.subsystem_abbr,
                  req_test.c.subsystem_name],
                and_(req_test.c.version_date >= today,~req_test.c.id.in_(select([req_his.c.test_id]))))
    
    req_rs = req.execute()
    data = {}
    for row in req_rs:
        row = list(row)
        
        #print 'row',row
        
        data['test_id']           = row[0]
        data['system']            = row[1] 
        data['function_name']     = row[2] 
        data['function_code']     = row[3] 
        data['function_type']     = row[4] 
        data['change_status']     = row[5] 
        data['using_status']      = row[6] 
        data['function_point']    = row[7] 
        data['business_degree']   = row[8] 
        data['trading_frequency'] = row[9] 
        data['across_system']     = row[10]
        data['trading_origin']    = row[11]
        data['trading_route']     = row[12]
        data['daily_visit']       = row[13]
        data['test_version']      = row[14]
#        data['function_flag']     = row[15]
#        data['test_flag']         = row[16]
        data['version_date']      = row[15]
        data['test_status']       = row[16]
        data['function_endtime']  = row[17]
        data['lock_flag']         = row[18]
        data['modified_date']     = row[19]
        data['modifield_user']    = row[20]
        data['subsystem_abbr']    = row[21]
        data['subsystem_name']    = row[22]
                                
        #print sel_his

#        print 'his-row',row
        #print 'row_line',row
        r = req_his(**data)
        r.save()
    
    #------------------------------------    
    #计算测试案例变更次数
    
    
    deltaday = datetime.timedelta(days=60)
    req_change_num = select([func.count(func.distinct(func.date(req_his.c.modified_date))),
                    req_his.c.system,
                    req_his.c.version_date],
                    and_(req_his.c.version_date >= today)
                ).group_by(req_his.c.system,
                            req_his.c.version_date
                            )
    
    req_change_num_rs = req_change_num.execute()
    data_rate ={}
        
    for row in req_change_num_rs:
        num,system,version_date = row
        data_rate['system'] = system
        data_rate['version_date'] = version_date
        data_rate['change_num'] = num - 1
        
        #计算system,version_date的总案例数
        sel_total = select([func.count(func.distinct(req_test.c.id))],
                            and_(req_test.c.version_date >= version_date,
                                req_test.c.system == system,
                                )).group_by(req_test.c.system,
                                            req_test.c.version_date)
        sel_total_rs = sel_total.execute()
        total_num = 0
        for row in sel_total_rs:
            total_num, = row
            #data_rate['change_num'] = num
        #--------------------------------------
        #计算system,version_date测试通过的案例数
        sel_finish = select([func.count(func.distinct(req_test.c.id))],
                            and_(req_test.c.version_date >= version_date,
                                req_test.c.system == system,
                                req_test.c.test_status == '1')
                            ).group_by(req_test.c.system,
                                       req_test.c.version_date)
#        print 'sel_finish',sel_finish
        sel_finish_rs = sel_finish.execute()
        finish_num = 0
        for row in sel_finish_rs:
            finish_num, = row
        if total_num == 0 or finish_num == 0:
            change_num = 0
        else:
            change_num = float(finish_num)/total_num
        data_rate['test_finish_rate'] = change_num
        #--------------------------------------
        #计算system,version_date需求封版时间
        if version_date - today <= deltaday and version_date >= today:
             m_date = select([func.max(req_his.c.modified_date)],
                               and_(req_his.c.version_date == version_date,
                                    req_his.c.system == system))
             print m_date
             m_date_rs = m_date.execute()
             for row in m_date_rs:
                max_date, = row
             data_rate['req_modified_date'] = max_date
        else:
            data_rate['req_modified_date'] = None    
        
        #--------------------------------------
        req_rate.remove(and_(req_rate.c.system == system,
                            req_rate.c.version_date == version_date))
        
        s = req_rate(**data_rate)
        s.save()
        
if __name__ == '__main__':
    try:
        db.begin()
        run()
        db.commit()
    except:
        db.rollback()
        raise
